package repositorio;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import dados.Central;
import dados.Especialista;

public class RepositorioEspecialista extends SentencasBD {
	
	public RepositorioEspecialista(ConexaoBD conexao) {
		super(conexao, 1, 2, 2, 3);
		
		this.cadastro[0] = "INSERT INTO tb_especialista (id, sexo, nome, rg, cpf, data_nas, endereco," + 
							" formacao) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
		this.atualizacao[0] = "UPDATE tb_especialista SET centrais = ?, sexo = ? WHERE id = ?";
		this.atualizacao[1] = "UPDATE tb_especialista SET foto = ? WHERE id = ?";
		this.consulta[0] = "SELECT * FROM tb_especialista E WHERE E.id = ?";
		this.consulta[1] = "SELECT M.id, M.nome, M.cpf, M.rg FROM tb_especialista M ORDER BY M.nome";
		this.consulta[2] = "SELECT M.id, M.nome, M.cpf, M.rg FROM tb_especialista M WHERE M.nome LIKE ? ORDER BY M.nome";
		this.remocao[0] = "DELETE FROM tb_especialista WHERE id = ?";
	}
	
	public void inserir(Especialista e) throws SQLException {
		if(e != null){
			Connection c = conexao.getConexao();
			PreparedStatement ps = c.prepareStatement(this.cadastro[0]);
			ps.setInt(1, e.getId());
			ps.setString(2, ""+ e.getSexo());
			ps.setString(3, e.getNome());
			ps.setLong(4, e.getRg());
			ps.setLong(5, e.getCpf());
			ps.setDate(6, e.getDataNas());
			ps.setString(7, e.getEndereco());
			ps.setString(8, e.getFormacao());
			
			ps.executeUpdate();
			ps.close();
			c.close();
			
			if(e.getFoto() != null){
				c = conexao.getConexao();
				ps = c.prepareStatement(this.atualizacao[1]);
				ps.setBytes(1, e.getFoto());
				ps.setInt(2, e.getId());
				ps.executeUpdate();
				ps.close();
				c.close();
			}
		}
		else
			throw new IllegalArgumentException();
	}
	
	public void editar(Especialista e) throws SQLException{
		if(e != null){
			Connection c = conexao.getConexao();
			String sql = "UPDATE tb_especialista SET sexo = '" + e.getSexo() + "', " + 
						 "nome = '" + e.getNome() + "', rg = " + e.getRg() + ", cpf = " + e.getCpf() + 
						 ", " + "data_nas = ?, endereco = '" + e.getEndereco() + "', formacao = '" +
						 e.getFormacao() + "', centrais = ";
			
			if(e.getCentralAnalises() != null){ 
				String centrais = "tp_nt_central(";
				for(int i = 0; i < e.getCentralAnalises().size(); i++){
					Central ce = e.getCentralAnalises().get(i);
					centrais += "(SELECT REF(C) FROM tb_central C WHERE C.id = " + ce.getId() + ")";
					if(i != e.getCentralAnalises().size()-1)
						centrais += ",";
				}
				centrais += ")";
				sql += centrais + " ";
			}
			else
				sql += "tp_nt_central() ";
			
			sql += "WHERE id = " + e.getId();			
			//System.out.println("--" + sql);
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setDate(1, e.getDataNas());
			ps.executeUpdate();
			ps.close();
			c.close();
			
			if(e.getFoto() != null){
				c = conexao.getConexao();
				ps = c.prepareStatement(this.atualizacao[1]);
				ps.setBytes(1, e.getFoto());
				ps.setInt(2, e.getId());
				ps.executeUpdate();
				ps.close();
				c.close();
			}
		}
		else
			throw new IllegalArgumentException();
	}
	
	public void remover(int id) throws SQLException{
		Connection c = conexao.getConexao();
		PreparedStatement ps = c.prepareStatement(this.remocao[0]);
		ps.setInt(1, id);
		ps.executeUpdate();
		ps.close();
		c.close();
	}
	
	public Iterator<Especialista> iterator() throws SQLException {
		PreparedStatement ps = conexao.getConexao().prepareStatement(consulta[1]);
		ResultSet set = ps.executeQuery();
		List<Especialista> all = new ArrayList<Especialista>();
		Especialista temp = null;
		while(set.next()){
			temp = new Especialista(set.getInt(1), set.getString(2), set.getLong(3), set.getLong(4));
			all.add(temp);
		}
		
		return all.iterator();
	}
	
	private List<Central> pegarCentrais(int id) throws SQLException{
		String sql = "SELECT DEREF(ss.COLUMN_VALUE).id, DEREF(ss.COLUMN_VALUE).numero_licenca, DEREF(ss.COLUMN_VALUE).nome, " +
				"DEREF(ss.COLUMN_VALUE).endereco FROM TABLE(SELECT C.centrais FROM tb_especialista C WHERE C.id = ?) ss";
		PreparedStatement ps = conexao.getConexao().prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet set = ps.executeQuery();
		Central temp = null;
		List<Central> centrais = new ArrayList<Central>();
		while(set.next()){
			temp = new Central(set.getInt(1), set.getLong(2), set.getString(3), set.getString(4));
			centrais.add(temp);
		}
		
		return centrais;
	}
	
	public Especialista buscar(int id) throws SQLException{
		PreparedStatement ps = conexao.getConexao().prepareStatement(consulta[0]);
		ps.setInt(1, id);
		ResultSet set = ps.executeQuery();
		Especialista temp = null;
		if(set.next()){
			temp = new Especialista(set.getInt(1), set.getString(2).charAt(0), set.getString(3),
				set.getLong(4), set.getLong(5), set.getDate(6), set.getString(7), 
				set.getString(9), set.getBytes(8)); 
			
			temp.setCentralAnalises(this.pegarCentrais(id));
		}
		return temp;
	}
	
	public Iterator<Especialista> buscar(String nome) throws SQLException{
		PreparedStatement ps = conexao.getConexao().prepareStatement(consulta[2]);
		ps.setString(1, "%" + nome + "%");
		ResultSet set = ps.executeQuery();
		List<Especialista> all = new ArrayList<Especialista>();
		Especialista temp = null;
		while(set.next()){
			temp = new Especialista(set.getInt(1), set.getString(2), set.getLong(3), set.getLong(4));
			all.add(temp);
		}
		
		return all.iterator();
	}
	
	
	/*
	public static void main(String[] args) {
		try {
			ConexaoBD c = new ConexaoBD("oracle.jdbc.driver.OracleDriver",
					"jdbc:oracle:thin:@itapissuma.cin.ufpe.br:1521:dbdisc", 
					"g101if685_eq02", "equipe2gdi");
			System.out.println("passou daqui 1");
			RepositorioEspecialista r = new RepositorioEspecialista(c);
			
			File foto = new File("C:\\Users\\lgnfl\\Downloads\\foto.jpg");
			FileInputStream in = new FileInputStream(foto);
			byte[] fileContent = new byte[(int)foto.length()];
			in.read(fileContent);
			System.out.println("passou daqui 2");
			Especialista e = new Especialista(10106, 'M', "nomeN", 7700884, 681584, new Date(454546987), 
																	"enderecoN", "formacaoN", fileContent);
			System.out.println("passou daqui 3");
			r.inserir(e);
			r.inserir(new Especialista(10105, 'M', "nome44", 7750884, 681854, new Date(454546987), "4", "1", null));
			r.inserir(new Especialista(10104, 'F', "nome12", 7700774, 681684, new Date(454546987), "4", "2", null));
			System.out.println("INSERIU: " + e);
			
			Especialista teste = r.buscar(10106);
			System.out.println("BUSCOU: " + teste);
			boolean flag = false;
			for(int i = 0; i < fileContent.length && !flag; i++){
				if(fileContent[i] != teste.getFoto()[i])
					flag = true;
			}
			if(!flag) System.out.println("FOTO ARMAZENANDO OK");
			
			e.adicionarCentral(new Central(1456, 5, null, null));
			e.adicionarCentral(new Central(1457, 8, null, null));
			r.editar(e);
			System.out.println("MANDOU EDITAR COM CENTRAIS");
			teste = r.buscar(10106);
			System.out.println("- Centrais que foram adicionadas:");
			for(Central cen : teste.getCentralAnalises())
				System.out.println(cen.getNome() + " " + cen.getId());
			
			System.out.println("- Todos cadastrados:");
			Iterator<Especialista> it = r.iterator();
			while(it.hasNext()) System.out.println(it.next());
			
			System.out.println("- Busca por 'ome':");
			it = r.buscar("ome");
			while(it.hasNext()) System.out.println(it.next());
			
			r.remover(10106);
			teste = r.buscar(10106);
			if(teste == null) System.out.println("REMOVEU");
			   
		} catch (ClassNotFoundException e) {
			System.out.println(">> erro1");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println(">> erro2");
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			System.out.println(">> erro3");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println(">> erro4");
			e.printStackTrace();
		}
	}*/
}
